package com.zhentao.datacollection.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.zhentao.datacollection.domain.entity.StorageStatistics;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.time.LocalDate;
import java.util.List;

/**
 * 入库统计Mapper
 */
@Mapper
public interface StorageStatisticsMapper extends BaseMapper<StorageStatistics> {

    /**
     * 分页查询入库统计列表 - 基础查询
     */
    @Select("SELECT * FROM dc_storage_statistics WHERE is_delete = 0 ORDER BY clearance_date DESC, storage_time DESC")
    IPage<StorageStatistics> selectPageWithConditions(Page<StorageStatistics> page,
                                                     @Param("startDate") LocalDate startDate,
                                                     @Param("endDate") LocalDate endDate,
                                                     @Param("dataType") String dataType,
                                                     @Param("storageStatus") String storageStatus);

    /**
     * 查询指定月份的金额统计
     */
    @Select("SELECT COALESCE(SUM(rmb_amount), 0) FROM dc_storage_statistics " +
            "WHERE YEAR(clearance_date) = #{year} AND MONTH(clearance_date) = #{month} " +
            "AND data_type = #{dataType} AND storage_status = 'SUCCESS' AND is_delete = 0")
    BigDecimal getMonthlyAmount(@Param("year") int year, @Param("month") int month, @Param("dataType") String dataType);

    /**
     * 查询去年同月金额
     */
    @Select("SELECT COALESCE(SUM(rmb_amount), 0) FROM dc_storage_statistics " +
            "WHERE YEAR(clearance_date) = #{year} AND MONTH(clearance_date) = #{month} " +
            "AND data_type = #{dataType} AND storage_status = 'SUCCESS' AND is_delete = 0")
    BigDecimal getLastYearSameMonthAmount(@Param("year") int year, @Param("month") int month, @Param("dataType") String dataType);

    /**
     * 查询上月金额
     */
    @Select("SELECT COALESCE(SUM(rmb_amount), 0) FROM dc_storage_statistics " +
            "WHERE YEAR(clearance_date) = #{year} AND MONTH(clearance_date) = #{month} " +
            "AND data_type = #{dataType} AND storage_status = 'SUCCESS' AND is_delete = 0")
    BigDecimal getLastMonthAmount(@Param("year") int year, @Param("month") int month, @Param("dataType") String dataType);

    /**
     * 获取所有数据类型
     */
    @Select("SELECT DISTINCT data_type FROM dc_storage_statistics WHERE is_delete = 0 ORDER BY data_type")
    List<String> getAllDataTypes();

    /**
     * 获取统计概览
     */
    @Select("SELECT COUNT(*) as record_count, " +
            "COALESCE(SUM(CASE WHEN storage_status = 'SUCCESS' THEN rmb_amount ELSE 0 END), 0) as rmb_amount, " +
            "COALESCE(SUM(CASE WHEN storage_status = 'SUCCESS' THEN usd_amount ELSE 0 END), 0) as usd_amount " +
            "FROM dc_storage_statistics WHERE is_delete = 0")
    StorageStatistics getStatisticsOverview(@Param("startDate") LocalDate startDate, @Param("endDate") LocalDate endDate);

    /**
     * 导出查询（不分页）
     */
    @Select("SELECT * FROM dc_storage_statistics WHERE is_delete = 0 " +
            "ORDER BY clearance_date DESC, storage_time DESC LIMIT #{maxRecords}")
    List<StorageStatistics> selectForExport(@Param("startDate") LocalDate startDate,
                                           @Param("endDate") LocalDate endDate,
                                           @Param("dataType") String dataType,
                                           @Param("storageStatus") String storageStatus,
                                           @Param("maxRecords") int maxRecords);
}
